package com.zerowidth.db.op;

import com.zerowidth.db.DBHelper;
import com.zerowidth.db.bean.BabyBean;
import com.zerowidth.db.bean.RelationBean;
import com.zerowidth.db.bean.UserBean;
import com.zerowidth.db.bean.vm.BabyRelationVM;
import com.zerowidth.db.bean.vm.FamilyVM;
import com.zerowidth.db.utils.IOUtils;
import com.zerowidth.db.utils.RandomUtils;
import com.zerowidth.db.utils.StringUtils;
import com.zerowidth.db.utils.TimeUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BabyRelationDB {

    public static BabyBean findBaby(String invite_code) {
        String sql = String.format("SELECT * FROM baby a WHERE invite_code ='%s'", invite_code);
        ResultSet rs = DBHelper.executeQuery(sql);
        if (rs == null) {
            return null;
        }

        try {
            if (rs.next()) {
                BabyBean babyBean = new BabyBean();
                babyBean.id = rs.getInt("a.id");
                babyBean.baby_id = rs.getString("a.baby_id");
                babyBean.nick_name = rs.getString("a.nick_name");
                babyBean.gender = rs.getInt("a.gender");
                babyBean.birthday = rs.getString("a.birthday");
                babyBean.avatar = rs.getString("a.avatar");
                babyBean.creator_user_id = rs.getString("a.creator_user_id");
                babyBean.creat_time = rs.getString("a.create_time");
                babyBean.update_time = rs.getString("a.update_time");
                babyBean.invite_code = rs.getString("a.invite_code");
                babyBean.baby_status = rs.getInt("a.baby_status");
                return babyBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(rs);
        }

        return null;
    }

    public static RelationBean findRelation(String userId, String baby_id) {
        String sql = String.format("SELECT * FROM relation b WHERE baby_id ='%s' and user_id = '%s'", baby_id, userId);
        ResultSet rs = DBHelper.executeQuery(sql);
        if (rs == null) {
            return null;
        }

        try {
            if (rs.next()) {
                RelationBean relationBean = new RelationBean();
                relationBean.id = rs.getInt("b.id");
                relationBean.baby_id = rs.getString("b.baby_id");
                relationBean.is_creator = rs.getBoolean("b.is_creator");
                relationBean.user_id = rs.getString("b.user_id");
                relationBean.relation = rs.getString("b.relation");
                relationBean.create_time = rs.getString("b.create_time");
                relationBean.update_time = rs.getString("b.update_time");
                relationBean.relation_status = rs.getInt("b.relation_status");
                relationBean.set_top = rs.getBoolean("b.set_top");
                return relationBean;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(rs);
        }

        return null;
    }

    public static RelationBean createBabyAndRelation(String creatorUserId, String relation, BabyBean babyBean) {

        //插入 baby 表
        String time = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);
        String inviteCode = RandomUtils.randomNumber(6);
        String sql = String.format("INSERT INTO baby (baby_id, nick_name, gender, birthday, avatar, " +
                        "creator_user_id, create_time, update_time, invite_code) " +
                        "VALUES ('%s', '%s', %d, '%s', '%s', '%s', '%s', '%s', '%s')",
                babyBean.baby_id, babyBean.nick_name, babyBean.gender, babyBean.birthday, babyBean.avatar,
                creatorUserId, time, time, inviteCode);

        babyBean.creat_time = time;
        babyBean.update_time = time;
        babyBean.creator_user_id = creatorUserId;
        babyBean.invite_code = inviteCode;

        babyBean.id = DBHelper.executeInsert(sql);
        if (babyBean.id < 0) {
            return null;
        }

        //插入 relation 表
        RelationBean relationBean = new RelationBean();
        relationBean.baby_id = babyBean.baby_id;
        relationBean.is_creator = true;
        relationBean.user_id = creatorUserId;
        relationBean.relation = relation;
        relationBean.create_time = time;
        relationBean.update_time = time;
        relationBean.relation_status = 0;
        String sql2 = String.format("INSERT INTO relation (baby_id, is_creator, user_id, relation, create_time, " +
                        "update_time, relation_status) " +
                        "VALUES ('%s', %b, '%s', '%s', '%s', '%s', %d)",
                relationBean.baby_id, relationBean.is_creator, relationBean.user_id, relationBean.relation,
                relationBean.create_time, relationBean.update_time, relationBean.relation_status);
        DBHelper.executeInsert(sql2);

        return relationBean;
    }

    //查询指定宝宝的所有亲友
    public static List<FamilyVM> getBabyFamily(String baby_id) {
        List<FamilyVM> resultList = new ArrayList<>();

        String sql2 = String.format("SELECT c.creator_user_id, c.baby_id, c.relation, c.user_id, c.create_time, c.update_time, c.relation_status, " +
                " d.nick_name, d.gender, d.birthday, d.avatar, d.phone_number " +
                " FROM ( SELECT a.creator_user_id, b.baby_id, b.relation, b.user_id, b.create_time, b.update_time, b.relation_status" +
                " FROM baby a LEFT JOIN relation b ON a.baby_id = b.baby_id " +
                " WHERE a.baby_id = '%s' AND b.relation_status = 0) c" +
                " LEFT JOIN album_user d ON c.user_id =d.user_id ORDER BY c.create_time", baby_id);

        ResultSet rs2 = DBHelper.executeQuery(sql2);
        if (rs2 == null) {
            return resultList;
        }

        try {
            while (rs2.next()) {
                FamilyVM vm = new FamilyVM();
                vm.user_bean = new UserBean();
                vm.user_bean.user_id = rs2.getString("user_id");
                vm.user_bean.nick_name = rs2.getString("nick_name");
                vm.user_bean.avatar = rs2.getString("avatar");
                vm.user_bean.phone_number = rs2.getString("phone_number");
                vm.user_bean.gender = rs2.getInt("gender");
                vm.user_bean.birthday = rs2.getString("birthday");

                vm.relation_bean = new RelationBean();
                vm.relation_bean.baby_id = rs2.getString("baby_id");
                vm.relation_bean.is_creator = StringUtils.equals(rs2.getString("c.creator_user_id"), rs2.getString("c.user_id"));
                vm.relation_bean.relation = rs2.getString("c.relation");
                vm.relation_bean.create_time = rs2.getString("c.create_time");
                vm.relation_bean.relation_status = rs2.getInt("c.relation_status");

                resultList.add(vm);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.close(rs2);
        }

        return resultList;
    }

    public static List<BabyRelationVM> getBabyRelationVm(String user_id) {

        //第一步：查询此用户的所有有效宝宝，及关系数据
        String sql = String.format("SELECT * FROM baby a inner join relation b on a.baby_id = b.baby_id " +
                "where b.user_id = '%s' AND a.baby_status = 0 AND b.relation_status = 0", user_id);

        ResultSet rs = DBHelper.executeQuery(sql);

        if (rs == null) {
            return null;
        }

        List<BabyRelationVM> babyRelationVMList = new ArrayList<>();

        try {
            while (rs.next()) {
                BabyRelationVM vm = new BabyRelationVM();

                vm.baby_bean = new BabyBean();
                vm.baby_bean.id = rs.getInt("a.id");
                vm.baby_bean.baby_id = rs.getString("a.baby_id");
                vm.baby_bean.nick_name = rs.getString("a.nick_name");
                vm.baby_bean.gender = rs.getInt("a.gender");
                vm.baby_bean.birthday = rs.getString("a.birthday");
                vm.baby_bean.avatar = rs.getString("a.avatar");
                vm.baby_bean.creator_user_id = rs.getString("a.creator_user_id");
                vm.baby_bean.creat_time = rs.getString("a.create_time");
                vm.baby_bean.update_time = rs.getString("a.update_time");
                vm.baby_bean.invite_code = rs.getString("a.invite_code");

                vm.relation_bean = new RelationBean();
                vm.relation_bean.id = rs.getInt("b.id");
                vm.relation_bean.baby_id = rs.getString("b.baby_id");
                vm.relation_bean.is_creator = rs.getBoolean("b.is_creator");
                vm.relation_bean.user_id = rs.getString("b.user_id");
                vm.relation_bean.relation = rs.getString("b.relation");
                vm.relation_bean.create_time = rs.getString("b.create_time");
                vm.relation_bean.update_time = rs.getString("b.update_time");
                vm.relation_bean.relation_status = rs.getInt("b.relation_status");
                vm.relation_bean.set_top = rs.getBoolean("b.set_top");

                babyRelationVMList.add(vm);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            IOUtils.close(rs);
        }

        //第二步：查询所有宝宝的亲友
        for (int i = 0; i < babyRelationVMList.size(); i++) {
            BabyBean babyBean = babyRelationVMList.get(i).baby_bean;
            babyRelationVMList.get(i).family_list = getBabyFamily(babyBean.baby_id);
        }

        return babyRelationVMList;
    }

    //创建被邀请的家人关系
    public static RelationBean createInviteRelation(String userId, String babyId, String relation) {
        String time = TimeUtils.convert(System.currentTimeMillis(), TimeUtils.TIME_FORMAT_YYYY_MM_DD_HH_MM_SS);

        //插入 relation 表
        RelationBean relationBean = new RelationBean();
        relationBean.baby_id = babyId;
        relationBean.is_creator = false;
        relationBean.user_id = userId;
        relationBean.relation = relation;
        relationBean.create_time = time;
        relationBean.update_time = time;
        relationBean.relation_status = 0;
        String sql2 = String.format("INSERT INTO relation (baby_id, is_creator, user_id, relation, create_time, " +
                        "update_time, relation_status) " +
                        "VALUES ('%s', %b, '%s', '%s', '%s', '%s', %d)",
                relationBean.baby_id, relationBean.is_creator, relationBean.user_id, relationBean.relation,
                relationBean.create_time, relationBean.update_time, relationBean.relation_status);


        if (DBHelper.executeInsert(sql2) > 0) {
            return relationBean;
        }

        return null;
    }

    public static boolean updateTop(String userId, String babyId) {
        //先把此用户的所有宝宝 取消置顶
        String sql0 = String.format("UPDATE relation SET set_top = %b WHERE user_id = '%s'", false, userId);
        DBHelper.executeUpdate(sql0);

        //再把指定的宝宝置顶
        String sql1 = String.format("UPDATE relation SET set_top = %b WHERE user_id = '%s' and baby_id = '%s'",
                true, userId, babyId);
        return DBHelper.executeUpdate(sql1) > 0;
    }

    public static boolean deleteBaby(String userId, String babyId) {
        String sql1 = String.format("UPDATE baby SET baby_status = 1 WHERE creator_user_id = '%s' and baby_id = '%s'",
                userId, babyId);
        return DBHelper.executeUpdate(sql1) > 0;
    }

    public static boolean unFollowBaby(String userId, String babyId) {
        String sql1 = String.format("UPDATE relation SET relation_status = 1 WHERE user_id = '%s' and baby_id = '%s'",
                userId, babyId);
        return DBHelper.executeUpdate(sql1) > 0;
    }

    public static boolean updateBabyInfo(String babyId, String avatar, String nickName,
                                         String birthDay, int gender) {
        String sql0 = String.format("UPDATE baby SET avatar = '%s', nick_name = '%s', " +
                " birthday = '%s', gender = %d" +
                " WHERE baby_id = '%s'", avatar, nickName, birthDay, gender, babyId);
        return DBHelper.executeUpdate(sql0) > 0;
    }

}
